--001636c5971b841392047ee25aea
Content-Type: multipart/alternative; boundary=001636c5971b84138b047ee25ae8
--001636c5971b84138b047ee25ae8
Content-Type: text/plain; charset=ISO-8859-1
I'd like a way to allow non-superusers to cancel their own queries.
Wearing a DBA hat sometimes, I get requests from users asking that their
long-running or erroneous queries be killed. These users have typically
executed their queries through phpPgAdmin or some other method that doesn't
allow them to cancel their own query, as you can in a psql session by
hitting Control-C. Letting users kill their own queries would be quite
useful[1], as it would cut down on work for DBAs.
I couldn't find a way to implement such a function in a PL, so I hacked up
backend/utils/adt/misc.c so that:
* pg_signal_backend() no longer restricts calls to users passing the
superuser() check
* callers of pg_signal_backend(), which are just pg_cancel_backend() and
pg_terminate_backend() right now, must perform their own permissions checks
* pg_cancel_backend() lets users kill their own queries, or superusers kill
any query
Patch should be attached. This was my first attempt at modifying PG, so I
suspect there are some problems I'm not seeing in my simple approach, or
else someone would have already done this. Some questions:
* Is there anyone else who's attempted[2] to implement something similar to
what I'm looking for, or are others interested in having something like
this?
* Is there a fundamental reason why it's unsafe for non-superusers to be
sending SIGTERM to their own backends (in other words, is this idea fatally
flawed)?
Thanks for any help,
Josh
--
[1] MySQL offers similar functionality with the "KILL <thread_id>" command,
which lets unprivileged users kill queries running under their own usernames
(actually, it looks like it disconnects the killed session entirely). Oracle
also has ALTER SYSTEM KILL SESSION -- I'm not sure if unprivileged users are
allowed to use this.
[2] I found a thread from 2006 where Craig James was after similar
functionality, but he implemented a rather ugly solution, namely a C
function which would let any user send a SIGTERM to any backend PID. I'm not
sure why he didn't just use a PL/pgSQL wrapper around
pg_terminate_backend().
http://archives.postgresql.org/pgsql-performance/2006-07/msg 00092.php
--001636c5971b84138b047ee25ae8
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
I'd like a way to allow non-superusers to cancel their own queries.<br>=
<br>Wearing a DBA hat sometimes, I get requests from users asking that thei=
r long-running or erroneous queries be killed. These users have typically e=
xecuted their queries through phpPgAdmin or some other method that doesn=
9;t allow them to cancel their own query, as you can in a psql session by h=
itting Control-C. Letting users kill their own queries would be quite usefu=
l[1], as it would cut down on work for DBAs.<br>
<br>I couldn't find a way to implement such a function in a PL, so I ha=
cked up backend/utils/adt/misc.c so that:<br>=A0* pg_signal_backend() no lo=
nger restricts calls to users passing the superuser() check<br>=A0* callers=
of pg_signal_backend(), which are just pg_cancel_backend() and pg_terminat=
e_backend() right now, must perform their own permissions checks<br>
=A0* pg_cancel_backend() lets users kill their own queries, or superusers k=
ill any query<br><br>Patch should be attached. This was my first attempt at=
modifying PG, so I suspect there are some problems I'm not seeing in m=
y simple approach, or else someone would have already done this. Some quest=
ions:<br>
=A0* Is there anyone else who's attempted[2] to implement something sim=
ilar to what I'm looking for, or are others interested in having someth=
ing like this?<br>=A0* Is there a fundamental reason why it's unsafe fo=
r non-superusers to be sending SIGTERM to their own backends (in other word=
s, is this idea fatally flawed)?<br>
<br>Thanks for any help,<br>Josh<br><br>--<br>[1] MySQL offers similar func=
tionality with the "KILL <thread_id>" command, which lets u=
nprivileged users kill queries running under their own usernames (actually,=
it looks like it disconnects the killed session entirely). Oracle also has=
ALTER SYSTEM KILL SESSION -- I'm not sure if unprivileged users are al=
lowed to use this.<br>
<br>[2] I found a thread from 2006 where Craig James was after similar func=
tionality, but he implemented a rather ugly solution, namely a C function w=
hich would let any user send a SIGTERM to any backend PID. I'm not sure=
why he didn't just use a PL/pgSQL wrapper around pg_terminate_backend(=
). <br>
=A0=A0=A0 <a href=3D"http://archives.postgresql.org/pgsql-performance/2006-=
07/msg00092.php">http://archives.postgresql.org/pgsql-perfor mance/2006-07/m=
sg00092.php</a><br><br>
--001636c5971b84138b047ee25ae8--
--001636c5971b841392047ee25aea
Content-Type: application/octet-stream; name="pg_cancel_backend.patch"
Content-Disposition: attachment; filename="pg_cancel_backend.patch"
Content-Transfer-Encoding: base64
X-Attachment-Id: f_g5bknyxo0
ZGlmZiAtLWdpdCBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9taXNjLmMgYi9z
cmMvYmFja2VuZC91dGlscy9hZHQvbWlzYy5jCmluZGV4IDMwMTk5YjQuLmYx
OWMyZTUgMTAwNjQ0CioqKiBhL3NyYy9iYWNrZW5kL3V0aWxzL2FkdC9taXNj
LmMKLS0tIGIvc3JjL2JhY2tlbmQvdXRpbHMvYWR0L21pc2MuYwoqKioqKioq
KioqKioqKioKKioqIDMzLDM4ICoqKioKLS0tIDMzLDM5IC0tLS0KICAjaW5j
bHVkZSAic3RvcmFnZS9wcm9jYXJyYXkuaCIKICAjaW5jbHVkZSAidXRpbHMv
YnVpbHRpbnMuaCIKICAjaW5jbHVkZSAidGNvcC90Y29wcHJvdC5oIgorICNp
bmNsdWRlICJzdG9yYWdlL3Byb2MuaCIKICAKICAjZGVmaW5lIGF0b29pZCh4
KSAgKChPaWQpIHN0cnRvdWwoKHgpLCBOVUxMLCAxMCkpCiAgCioqKioqKioq
KioqKioqKiBjdXJyZW50X3F1ZXJ5KFBHX0ZVTkNUSU9OX0FSR1MpCioqKiA2
OCw4MyAqKioqCiAgCQlQR19SRVRVUk5fTlVMTCgpOwogIH0KICAKICAvKgog
ICAqIEZ1bmN0aW9ucyB0byBzZW5kIHNpZ25hbHMgdG8gb3RoZXIgYmFja2Vu
ZHMuCiAgICovCiAgc3RhdGljIGJvb2wKICBwZ19zaWduYWxfYmFja2VuZChp
bnQgcGlkLCBpbnQgc2lnKQogIHsKLSAJaWYgKCFzdXBlcnVzZXIoKSkKLSAJ
CWVyZXBvcnQoRVJST1IsCi0gCQkJCShlcnJjb2RlKEVSUkNPREVfSU5TVUZG
SUNJRU5UX1BSSVZJTEVHRSksCi0gCQkJKGVycm1zZygibXVzdCBiZSBzdXBl
cnVzZXIgdG8gc2lnbmFsIG90aGVyIHNlcnZlciBwcm9jZXNzZXMiKSkpKTsK
ICAKICAJaWYgKCFJc0JhY2tlbmRQaWQocGlkKSkKICAJewotLS0gNjksODEg
LS0tLQogIAkJUEdfUkVUVVJOX05VTEwoKTsKICB9CiAgCisgCiAgLyoKICAg
KiBGdW5jdGlvbnMgdG8gc2VuZCBzaWduYWxzIHRvIG90aGVyIGJhY2tlbmRz
LgogICAqLwogIHN0YXRpYyBib29sCiAgcGdfc2lnbmFsX2JhY2tlbmQoaW50
IHBpZCwgaW50IHNpZykKICB7CiAgCiAgCWlmICghSXNCYWNrZW5kUGlkKHBp
ZCkpCiAgCXsKKioqKioqKioqKioqKioqIHBnX3NpZ25hbF9iYWNrZW5kKGlu
dCBwaWQsIGludCBzaWcpCioqKiAxMDUsMTIwICoqKioKICAJcmV0dXJuIHRy
dWU7CiAgfQogIAogIERhdHVtCiAgcGdfY2FuY2VsX2JhY2tlbmQoUEdfRlVO
Q1RJT05fQVJHUykKICB7CiEgCVBHX1JFVFVSTl9CT09MKHBnX3NpZ25hbF9i
YWNrZW5kKFBHX0dFVEFSR19JTlQzMigwKSwgU0lHSU5UKSk7CiAgfQogIAog
IERhdHVtCiAgcGdfdGVybWluYXRlX2JhY2tlbmQoUEdfRlVOQ1RJT05fQVJH
UykKICB7CiEgCVBHX1JFVFVSTl9CT09MKHBnX3NpZ25hbF9iYWNrZW5kKFBH
X0dFVEFSR19JTlQzMigwKSwgU0lHVEVSTSkpOwogIH0KICAKICBEYXR1bQot
LS0gMTAzLDE2MCAtLS0tCiAgCXJldHVybiB0cnVlOwogIH0KICAKKyAKKyAv
KiBBbGxvdyB1c2VycyB0byBjYW5jZWwgdGhlaXIgb3duIHF1ZXJpZXMgcnVu
bmluZyB1bmRlciB0aGUgZ2l2ZW4gYmFja2VuZCBQSUQuCisgICogU3VwZXJ1
c2VycyBtYXkgY2FuY2VsIGFueW9uZSdzIHF1ZXJpZXMuCisgICovCiAgRGF0
dW0KICBwZ19jYW5jZWxfYmFja2VuZChQR19GVU5DVElPTl9BUkdTKQogIHsK
ISAJaW50CQkJQmFja2VuZFBpZCA9IFBHX0dFVEFSR19JTlQzMigwKTsKISAJ
T2lkCQkJU2Vzc2lvblVzZXJJZCA9IEdldFVzZXJJZCgpOwohIAlQR1BST0MJ
ICAgKmJhY2tlbmRfcHJvYyA9IEJhY2tlbmRQaWRHZXRQcm9jKEJhY2tlbmRQ
aWQpOwohIAlPaWQJCQlUYXJnZXRSb2xlSWQ7CiEgCiEgCWlmIChiYWNrZW5k
X3Byb2MgPT0gTlVMTCkKISAJewohIAkJZXJlcG9ydChXQVJOSU5HLCAoZXJy
bXNnKCJVbmFibGUgdG8gZmluZCBiYWNrZW5kIHdpdGggUElEOiAldSIsIEJh
Y2tlbmRQaWQpKSk7CiEgCQlQR19SRVRVUk5fQk9PTChmYWxzZSk7CiEgCX0K
ISAJVGFyZ2V0Um9sZUlkID0gYmFja2VuZF9wcm9jLT5yb2xlSWQ7CiEgCiEg
CWVyZXBvcnQoTk9USUNFLCAoZXJybXNnKCJZb3VyIHNlc3Npb24gSUQ6ICV1
LiBEZXNpcmVkIGJhY2tlbmQgdG8ga2lsbDogJXUuIHJvbGVJZCB3aGljaCBp
cyB1c2luZyB0aGlzIGJhY2tlbmQ6ICV1IiwgKGludCkgU2Vzc2lvblVzZXJJ
ZCwgKGludCkgQmFja2VuZFBpZCwgKGludCkgVGFyZ2V0Um9sZUlkKSkpOwoh
IAohIAlpZiAoc3VwZXJ1c2VyKCkpCiEgCXsKISAJCWVyZXBvcnQoTk9USUNF
LCAoZXJybXNnKCJzdXBlcnVzZXIoKSBjaGVjayBwYXNzZWQhIikpKTsKISAJ
CVBHX1JFVFVSTl9CT09MKHBnX3NpZ25hbF9iYWNrZW5kKEJhY2tlbmRQaWQs
IFNJR0lOVCkpOwohIAl9CiEgCWVsc2UgaWYgKFRhcmdldFJvbGVJZCA9PSBT
ZXNzaW9uVXNlcklkKQohIAl7CiEgCQllcmVwb3J0KE5PVElDRSwgKGVycm1z
Zygicm9sZUlkcyBtYXRjaCwgc28geW91IG1heSBraWxsLiIpKSk7CiEgCQlQ
R19SRVRVUk5fQk9PTChwZ19zaWduYWxfYmFja2VuZChCYWNrZW5kUGlkLCBT
SUdJTlQpKTsKISAJfQohIAllbHNlCiEgCXsKISAJCWVyZXBvcnQoV0FSTklO
RywgKGVycm1zZygiWW91IG1heSBub3Qga2lsbCB0aGUgZGVzaXJlZCBiYWNr
ZW5kLCBzaW5jZSB5b3UgYXJlIG5vdCBzdXBlcnVzZXIgYW5kIHlvdXIgcm9s
ZUlkIGRpZmZlcnMuIikpKTsKISAJCVBHX1JFVFVSTl9CT09MKGZhbHNlKTsK
ISAJfQogIH0KICAKICBEYXR1bQogIHBnX3Rlcm1pbmF0ZV9iYWNrZW5kKFBH
X0ZVTkNUSU9OX0FSR1MpCiAgewohIAlpZiAoIXN1cGVydXNlcigpKQohIAl7
CiEgCQllcmVwb3J0KEVSUk9SLAohIAkJCQkoZXJyY29kZShFUlJDT0RFX0lO
U1VGRklDSUVOVF9QUklWSUxFR0UpLAohIAkJIChlcnJtc2coIm11c3QgYmUg
c3VwZXJ1c2VyIHRvIHRlcm1pbmF0ZSBvdGhlciBzZXJ2ZXIgcHJvY2Vzc2Vz
IikpKSk7CiEgCQlQR19SRVRVUk5fQk9PTChmYWxzZSk7CiEgCX0KISAJZWxz
ZQohIAl7CiEgCQlQR19SRVRVUk5fQk9PTChwZ19zaWduYWxfYmFja2VuZChQ
R19HRVRBUkdfSU5UMzIoMCksIFNJR1RFUk0pKTsKISAJfQogIH0KICAKICBE
YXR1bQo=
--001636c5971b841392047ee25aea
Content-Type: text/plain
Content-Disposition: inline
MIME-Version: 1.0
Content-Transfer-Encoding: quoted-printable
--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
--001636c5971b841392047ee25aea--
